
package com.productmodule.dao;
import com.productmodule.bean.TbProduct;
import com.productmodule.bean.TbProductType;
import java.sql.*;
import java.util.*;

import util.*;
public class TbProductTypeDAO {
    private Connection conn = null;
    private PreparedStatement pt = null;
    private ResultSet rs = null;
    private boolean flag = false;
    private String sql = "";
    private TbProductType tbProductType=null;
    private List<TbProductType> tbProductTypeList=null;

    public boolean add(TbProductType inTbProductType){
        try{
        conn=ConnectDB.getSqlServerConnection();
        sql="insert into Tb_ProductType(ProductTypeName,ParentTypeId,IsMaxProductType,ProductTypeMemo) values(?,?,?,?)";
        pt = conn.prepareStatement(sql);
        pt.setString(1,inTbProductType.getProductTypeName());
        if(inTbProductType.getParentTypeId()==0){
        	pt.setNull(2, Types.INTEGER);
        	pt.setInt(3,0);
        }
        else{
        	pt.setInt(2,inTbProductType.getParentTypeId());
        	pt.setInt(3,1);
        }
        pt.setString(4,inTbProductType.getProductTypeMemo());
        if (pt.executeUpdate() > 0) flag=true;
        }catch(Exception e){
            e.printStackTrace();
        }finally {
            ConnectDB.closeSqlConnection();
        }
        return flag;
    }
    public boolean del(int inProductTypeId){
        return UtilDAO.del("Tb_ProductType", "ProductTypeId", inProductTypeId);
    }
    public boolean edit(TbProductType inTbProductType){
        try{
        conn=ConnectDB.getSqlServerConnection();
        sql="update Tb_ProductType set ProductTypeName=?,ParentTypeId=?,IsMaxProductType=?,ProductTypeMemo=? where ProductTypeId=?";
        pt = conn.prepareStatement(sql);
        pt.setString(1,inTbProductType.getProductTypeName());
        if(inTbProductType.getParentTypeId()==0){
        	pt.setNull(2, Types.INTEGER);
        	pt.setInt(3, 0);
        }else{
        	pt.setInt(2,inTbProductType.getParentTypeId());
        	pt.setInt(3, 1);
        }
        pt.setString(4,inTbProductType.getProductTypeMemo());
        pt.setInt(5,inTbProductType.getProductTypeId());
        if (pt.executeUpdate() > 0) flag=true;
        }catch(Exception e){
            e.printStackTrace();
        }finally {
            ConnectDB.closeSqlConnection();
        }
        return flag;
    }
    
    public TbProductType getTbProductTypeByProductTypeId(int inProductTypeId) {
        tbProductType=null;
        try{
            conn = ConnectDB.getSqlServerConnection();
            sql = "select * from Tb_ProductType where ProductTypeId=? ";
            pt = conn.prepareStatement(sql);
            pt.setInt(1,inProductTypeId);
            rs = pt.executeQuery();
            while (rs.next()) {
               tbProductType = getTbProductType(rs);
            }
        }catch(Exception e){
            e.printStackTrace();
        }finally {
            ConnectDB.closeSqlConnection();
        }
        return tbProductType;
    }
    public List<TbProductType> getTbProductTypeByParentTypeId(int inProductTypeId) {
    	 tbProductTypeList=new ArrayList<TbProductType>();
        try{
            conn = ConnectDB.getSqlServerConnection();
            sql = "select * from Tb_ProductType where ParentTypeId=? ";
            pt = conn.prepareStatement(sql);
            pt.setInt(1,inProductTypeId);
            rs = pt.executeQuery();
            while (rs.next()) {
                tbProductTypeList.add(getTbProductType(rs));
            }
        }catch(Exception e){
            e.printStackTrace();
        }finally {
            ConnectDB.closeSqlConnection();
        }
        return tbProductTypeList;
    }
    public List<TbProductType> getTbProductTypeAll() {
        tbProductTypeList=new ArrayList<TbProductType>();
        try{
            conn = ConnectDB.getSqlServerConnection();
            sql = "select * from Tb_ProductType";
            pt = conn.prepareStatement(sql);
            rs = pt.executeQuery();
            while (rs.next()) {
                tbProductTypeList.add(getTbProductType(rs));
            }
        }catch(Exception e){
            e.printStackTrace();
        }finally {
            ConnectDB.closeSqlConnection();
        }
        return tbProductTypeList;
    }
    public List<TbProductType> getTbProductTypeAll(VskPage vp) {
        tbProductTypeList=new ArrayList<TbProductType>();
        try{
            conn = ConnectDB.getSqlServerConnection();
            sql = "select * from Tb_ProductType";
            vp.setAllCountSql(sql);
            vp.getAllRecordCount(conn, vp.getAllCountSql());
            pt = conn.prepareStatement(vp.processPageSql( "Tb_ProductType", "ProductTypeId"));
            rs = pt.executeQuery();
            while (rs.next()) {
                tbProductTypeList.add(getTbProductType(rs));
            }
        }catch(Exception e){
            e.printStackTrace();
        }finally {
            ConnectDB.closeSqlConnection();
        }
        return tbProductTypeList;
    }
    
    public String getProductType(int productTypeId,StringBuffer sb){
    	StringBuffer result = null;
    	if(sb==null){
    		result = new StringBuffer();
    	}else
    		result = sb;
    	 try {
			conn = ConnectDB.getSqlServerConnection();
			sql="select * from Tb_ProductType where ProductTypeId="+productTypeId;
			pt = conn.prepareStatement(sql);
			rs = pt.executeQuery();
			if(rs.next()){
				TbProductType productType = this.getTbProductType(rs);
				result.append(String.valueOf(productType.getProductTypeId())+",");
				if(productType.getParentTypeId()>0){
					this.getProductType(productType.getParentTypeId(), result);
				}
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
            ConnectDB.closeSqlConnection();
        }
    	return result.toString();
    }
    
    /**
     * 根据产品类型ID集合得到对象集合
     *@author qingyu zhang
     *@function:
     * @param productTypeIds
     * @return
     *2011-7-4
     *下午03:17:37
     *ProjectManagerT
     *com.productmodule.dao
     *List<TbProduct>
     */
    public List<TbProductType> getProductTypeByProductTypeId(String productTypeIds){
 	   tbProductTypeList = new ArrayList<TbProductType>();
 	   try {
 		conn = ConnectDB.getSqlServerConnection();
 		sql = "select * from Tb_ProductType  where ProductTypeId in("+productTypeIds+")";
 		pt = conn.prepareStatement(sql);
 		rs = pt.executeQuery();
 		while(rs.next()){
 			tbProductTypeList.add(this.getTbProductType(rs));
 		}
 	} catch (Exception e) {
 		e.printStackTrace();
 	}finally {
         ConnectDB.closeSqlConnection();      
     	}
 	return tbProductTypeList;
    }
    public String getTbProductTypeStringUitlTop(TbProductType tbProductType){
    	String str= "";
    	if(tbProductType.getTbProductTypeParent()==null)
    		return "0:"+tbProductType.getProductTypeId();
    	else{
    	flag=false;
    	str=tbProductType.getTbProductTypeParent().getProductTypeId()+":"+tbProductType.getProductTypeId();
    	while(!flag){
    		if(tbProductType.getTbProductTypeParent()!=null){
    			tbProductType=tbProductType.getTbProductTypeParent();
    			str=tbProductType.getParentTypeId()+":"+str;
    		}else
    			flag=true;
    	}
    	return str;
    	}
    	
    }
    public List<TbProductType> getTbProductTypeStringUitlTopl(TbProductType tbProductType){
    	List<TbProductType> tylist=new ArrayList<TbProductType>();
    	if(tbProductType.getTbProductTypeParent()==null)
    		tylist.add(tbProductType);
    	else{
    	flag=false;
    	tylist.add(tbProductType);
    	while(!flag){
    		if(tbProductType.getTbProductTypeParent()!=null){
    			tylist.add(tbProductType.getTbProductTypeParent());
    			tbProductType=tbProductType.getTbProductTypeParent();
    		}else
    			flag=true;
    	}
    	}
    	return tylist;
    	
    	
    }
    public TbProductType getTbProductType(ResultSet rs) throws Exception{
        TbProductType tbProductType = new TbProductType();
        tbProductType.setProductTypeId(rs.getInt("ProductTypeId"));
        tbProductType.setProductTypeLevel(rs.getInt("ProductTypeLevel"));
        tbProductType.setProductTypeName(rs.getString("ProductTypeName"));
        tbProductType.setParentTypeId(rs.getInt("ParentTypeId"));
        tbProductType.setIsMaxProductType(rs.getInt("IsMaxProductType"));
        tbProductType.setProductTypeMemo(rs.getString("ProductTypeMemo"));
        tbProductType.setCreateTime(rs.getString("CreateTime"));
        return tbProductType;
    }
    public static void main(String[] args) {
		TbProductType tp=new TbProductTypeDAO().getTbProductTypeByProductTypeId(17);
		//System.out.println(tp.getProductTypeName());
		List<TbProductType> tylist=new TbProductTypeDAO().getTbProductTypeStringUitlTopl(tp);
		for(TbProductType tpt:tylist){
			System.out.println(tpt.getProductTypeId());			
		}
	}
}
